﻿/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

--- Build the list of versions
DECLARE @t TABLE (ver_major_ro int, ver_minor_ro int, ver_description varchar(20))
INSERT INTO @t (ver_major_ro, ver_minor_ro, ver_description)
	SELECT	8, 0, 'SQL Server 2000'
	UNION SELECT 9, 0, 'SQL Server 2005'
	UNION SELECT 10, 0, 'SQL Server 2008'
	UNION SELECT 10, 5, 'SQL Server 2008 R2'
	UNION SELECT 11, 0, 'SQL Server 2012'

--- Update Changed Records
UPDATE		s
SET			ver_description = t.ver_description
FROM		@t t
JOIN		dbo.tblSQLVersion s ON t.ver_major_ro = s.ver_major_ro
			AND t.ver_minor_ro = s.ver_minor_ro
			AND t.ver_description <> s.ver_description

--- Insert Missing Records
INSERT INTO dbo.tblSQLVersion (ver_major_ro, ver_minor_ro, ver_description)
	SELECT		t.ver_major_ro,
				t.ver_minor_ro,
				t.ver_description
	FROM		@t t
	LEFT JOIN	dbo.tblSQLVersion s ON t.ver_major_ro = s.ver_major_ro
				AND t.ver_minor_ro = s.ver_minor_ro
	WHERE		s.ver_major_ro IS NULL